using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;  // .xlsx格式
using NPOI.HSSF.UserModel;  // .xls格式

public class ExcelReader
{
    public List<Dictionary<string, object>> ReadColumnsNtoY(string filePath, string sheetName)
    {
        var results = new List<Dictionary<string, object>>();

        using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook workbook = Path.GetExtension(filePath).ToLower() == ".xls" 
                ? (IWorkbook)new HSSFWorkbook(fs) 
                : new XSSFWorkbook(fs);

            ISheet sheet = workbook.GetSheet(sheetName) ?? 
                throw new ArgumentException($"工作表 {sheetName} 不存在");

            IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

            // 列范围设置（N=14列对应索引13，Y=25列对应索引24）
            const int startColIndex = 13; // N列
            const int endColIndex = 24;   // Y列

            for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                var rowData = new Dictionary<string, object>();
                
                for (int colIndex = startColIndex; colIndex <= endColIndex; colIndex++)
                {
                    ICell cell = row.GetCell(colIndex);
                    string columnName = GetColumnName(colIndex);
                    
                    // 获取单元格值（处理公式）
                    object cellValue = GetCellValueWithFormula(cell, evaluator);
                    
                    rowData[columnName] = cellValue;
                }
                
                results.Add(rowData);
            }
        }

        return results;
    }

    private object GetCellValueWithFormula(ICell cell, IFormulaEvaluator evaluator)
    {
        if (cell == null) return null;

        // 如果是公式单元格
        if (cell.CellType == CellType.Formula)
        {
            // 计算公式结果
            ICell evaluatedCell = evaluator.EvaluateInCell(cell);
            return GetCellValue(evaluatedCell);
        }

        return GetCellValue(cell);
    }

    private object GetCellValue(ICell cell)
    {
        if (cell == null) return null;

        return cell.CellType switch
        {
            CellType.Numeric => DateUtil.IsCellDateFormatted(cell) 
                ? cell.DateCellValue 
                : cell.NumericCellValue,
            CellType.String => cell.StringCellValue,
            CellType.Boolean => cell.BooleanCellValue,
            CellType.Formula => cell.NumericCellValue, // 已处理公式的情况
            _ => null
        };
    }

    private string GetColumnName(int colIndex)
    {
        int dividend = colIndex + 1; // 列索引从0开始，Excel列从1开始
        string columnName = string.Empty;

        while (dividend > 0)
        {
            int modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar('A' + modulo) + columnName;
            dividend = (dividend - modulo) / 26;
        }

        return columnName;
    }
}

// 使用示例：
var reader = new ExcelReader();
var data = reader.ReadColumnsNtoY("example.xlsx", "Sheet1");

// 打印结果
foreach (var row in data)
{
    Console.WriteLine("Row Data:");
    foreach (var kvp in row)
    {
        Console.WriteLine($"{kvp.Key}: {kvp.Value ?? "(null)"}");
    }
    Console.WriteLine(new string('-', 30));
}